package cn.fintecher.pangolin.service.report.repository;

import cn.fintecher.pangolin.entity.mysql.CaseInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * @Auther: xiaqun
 * @Description:
 * @Date: 15:13 2019/12/10
 */
public interface CaseInfoRepository extends JpaRepository<CaseInfo, String>, QuerydslPredicateExecutor<CaseInfo> {

    @Query(value="select f.departmentId,f.departmentName,f.departCode,f.userId as collecotId,f.real_name as collectorName,\n" +
                "\tmax(case f.case_data_status when 'IN_POOL' then f.count else 0 end)  collectCount,\n" +
                "\tmax(case f.case_data_status when 'IN_POOL' then f.sumLeftAmt else 0 end)  collectAmt\n" +
                "from (\n" +
                "select g.departmentId,g.departmentName,g.userId,g.real_name,g.case_data_status, sum(g.left_amt) as sumLeftAmt,count(case_data_status) as count,\n" +
                "g.departCode from (\n" +
                "SELECT u.real_name,c.left_amt,u.id as userId,c.case_data_status,d.id as departmentId,d.`name` as departmentName ,d.depart_code as departCode from \n" +
                "user u\n" +
                "LEFT JOIN case_info c on c.current_collector_id = u.id\n" +
                "LEFT JOIN department d on u.depart_id= d.id) g\n" +
                "GROUP BY g.userId,g.case_data_status) f\n" +
                "GROUP BY f.userId",nativeQuery = true)
    List<Object[]>  collectDataStatistics();

    @Query(value = "select id,current_collector_id from case_info where current_collector_id  in ?1 and " +
                    "pay_status='SETTLT' and clear_date =?2", nativeQuery = true)
    List<Object[]> querySettleCaseIds(Set<String> collectorIds,Date queryDate);

    @Query(value = "select id from case_info where current_collector_id = ?1\n",nativeQuery = true)
    List<String> queryCaseIds (String collectorId);

    @Query(value = "select sum(left_amt) from case_info where id in ?1\n",nativeQuery = true)
    double queryLeftAmountTotal(List<String> caseIds);
}
